package com.originskyseed.platform.common.utils;

import java.math.BigDecimal;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Excel操作工具
 *
 * @author Zhongj
 *
 */
public class ExcelUtil {

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	public static void createSheetHead(Sheet sheet, List<String> headList, CellStyle style) {
		Row row = sheet.createRow(0);
		for (int i = 0; i < headList.size(); i++) {
			String headStr = headList.get(i);
			Cell cell = row.getCell(i);
			cell = row.createCell(i);
			cell.setCellValue(headStr);
			cell.setCellStyle(style);
		}
	}

	public static void createRemark(Sheet sheet, String text, CellStyle style) {
		// 导出描述
		int lastRowNum = sheet.getLastRowNum() + 1;
		Row row = sheet.createRow(lastRowNum);
		CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
		sheet.addMergedRegion(region);
		ExcelUtil.rowCreateCellData(row, 0, text, style);
	}

	public static void createRemark(Sheet sheet, String text) {
		// 导出描述
		int lastRowNum = sheet.getLastRowNum() + 1;
		Row row = sheet.createRow(lastRowNum);
		CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);
		sheet.addMergedRegion(region);
		ExcelUtil.rowCreateCellData(row, 0, text);
	}

	/**
	 * 给行新增单元格并插入数据
	 * 
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 * @param style     单元格格式
	 */
	public static void rowCreateCellData(Row row, int cellIndex, String val, CellStyle style) {
		Cell cell = row.createCell(cellIndex);
		cell.setCellStyle(style);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 *
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowCreateCellData(Row row, int cellIndex, String val) {
		Cell cell = row.createCell(cellIndex);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 * 
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 * @param style     单元格格式
	 */
	public static void rowCreateCellData(Row row, int cellIndex, BigDecimal val, CellStyle style) {
		Cell cell = row.createCell(cellIndex);
		cell.setCellStyle(style);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 *
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowCreateCellData(Row row, int cellIndex, BigDecimal val) {
		Cell cell = row.createCell(cellIndex);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 *
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 * @param style     单元格格式
	 */
	public static void rowCreateCellData(Row row, int cellIndex, Double val, CellStyle style) {
		Cell cell = row.createCell(cellIndex);
		cell.setCellStyle(style);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 *
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowCreateCellData(Row row, int cellIndex, Double val) {
		Cell cell = row.createCell(cellIndex);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 * 
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 * @param style     单元格格式
	 */
	public static void rowCreateCellData(Row row, int cellIndex, Integer val, CellStyle style) {
		Cell cell = row.createCell(cellIndex);
		cell.setCellStyle(style);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行新增单元格并插入数据
	 *
	 * @param row       行队形
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowCreateCellData(Row row, int cellIndex, Integer val) {
		Cell cell = row.createCell(cellIndex);
		rowSetCellVal(row, cellIndex, val);
	}

	/**
	 * 给行指定单元格set数据
	 * 
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowSetCellVal(Row row, int cellIndex, String val) {
		Cell cell = row.getCell(cellIndex);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		cell.setCellValue(val == null ? "" : StringUtils.substring(val, 0, 32766));
	}

	/**
	 * 给行指定单元格set数据
	 * 
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowSetCellVal(Row row, int cellIndex, Double val) {
		Cell cell = row.getCell(cellIndex);
		cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		cell.setCellValue(val);
	}

	/**
	 * 给行指定单元格set数据
	 *
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowSetCellVal(Row row, int cellIndex, BigDecimal val) {
		Cell cell = row.getCell(cellIndex);
		cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		if (val != null) {
			cell.setCellValue(val.doubleValue());
		} else {
			cell.setCellValue(0.00D);
		}
	}

	/**
	 * 给行指定单元格set数据
	 * 
	 * @param cellIndex 单元格索引
	 * @param val       值
	 */
	public static void rowSetCellVal(Row row, int cellIndex, Integer val) {
		Cell cell = row.getCell(cellIndex);
		cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		cell.setCellValue(val == null ? 0 : val);
	}

	public static <T> T getCellValue(Row row, int i, Class<T> classType) {
		Object value = null;
		Cell cell = row.getCell(i);
		if (cell == null) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			value = cell.getStringCellValue();
		} else {
			if (String.class.getName().equals(classType.getName())) {
				cell.setCellType(CellType.STRING);
				value = cell.getStringCellValue();
			} else {
				value = cell.getNumericCellValue();
			}
		}

		if (value != null) {
			if (String.class.getName().equals(classType.getName())) {
				value = value.toString();
			} else if (Integer.class.getName().equals(classType.getName())) {
				if (Pattern.compile("^\\+{0,1}[1-9]\\d*").matcher(String.valueOf(value)).matches()) {
					value = Integer.valueOf(String.valueOf(value));
				} else {
					return null;
				}
			} else if (Double.class.getName().equals(classType.getName())) {
				if (Pattern.compile("[-+]{0,1}\\d+\\.\\d*|[-+]{0,1}\\d*\\.\\d+").matcher(String.valueOf(value)).matches()) {
					value = Double.valueOf(String.valueOf(value));
				}
				return null;
			} else if (BigDecimal.class.getName().equals(classType.getName())) {
				if (Pattern.compile("[-+]{0,1}\\d+\\.\\d*|[-+]{0,1}\\d*\\.\\d+").matcher(String.valueOf(value)).matches()) {
					value = new BigDecimal(String.valueOf(value));
				}
				return null;
			}
		}
		return (T) value;
	}

}
